CREATE Procedure [dbo].[Sp_Auto_OURow_Factor] @KindFlag Smallint, @FactorId BIGINT, @PersonPercent REAL  
As 

DECLARE @GoodsId Bigint
Declare @MaxPercent Real
Declare @MaxPrice Money
Declare @TotPrice Money
Declare @TmpPer Real
Declare @Price MONEY
DECLARE @Type SMALLINT
Declare @Id UNIQUEIDENTIFIER
Declare @DeviceShow TINYINT 
Declare @IsCalculateTax BIT 
DECLARE @IsDepencencies BIT 
DECLARE @ExchangeTax TINYINT = ISNULL((SELECT Value FROM dbo.GeneralSetting WHERE Parameter = 'Exchange_Tax'),0)

DECLARE @CalcIncrementalDeductionRow AS TABLE
(
   Id UNIQUEIDENTIFIER DEFAULT(NEWID()) PRIMARY KEY,
   GoodsId BIGINT,
   IncrementalDeductionCode INT ,
   Name NVARCHAR(30),
   Type CHAR(1),
   MaxPercent REAL,
   Maxprice   MONEY,
   IsDepencencies BIT ,
   DeviceShow TINYINT ,
   IsCalculateTax BIT ,
   [Percent] REAL DEFAULT(0),
   Qty MONEY,
   Price MONEY DEFAULT(0),
   GoodsPrice MONEY,
   TaxFlag CHAR(1)
)

INSERT Into @CalcIncrementalDeductionRow (GoodsId,IncrementalDeductionCode, Name, Type, MaxPercent, MaxPrice, IsDepencencies, DeviceShow, IsCalculateTax, Qty,Price, TaxFlag)
SELECT dbo.TmpHavaleh.AutoId GoodsId,
       Over_Under.AutoId,
       Name,
       Type,
       CASE
         WHEN ISNULL(Formula,'0') = 0 THEN Max_Percent
		 ELSE
           (CASE
		     WHEN KalaId.Duty_Flag = 1 THEN (
                                              Case
											    WHEN TaxPercent <> 0      AND Type = 1 THEN TaxPercent 
		                                        WHEN DiscountPercent <> 0 AND Type = 0 THEN DiscountPercent
												ELSE Max_Percent
											  END 
			                                )
		    ELSE 0
		   END) 
       END AS  Max_Percent,
       Max_Price,
	   ISNULL(dbo.Over_Under.Duty_Flag,0),
       ShowPrice,ISNULL(Formula,'0'),
       K_Qty1, 
       CASE
           WHEN Kind_Flag IN ( 5, 7, 9, 6, 0 ) THEN (CASE WHEN @ExchangeTax = 0 THEN Price_Forosh ELSE ((CASE WHEN (Price_Forosh < Price_KHarid)  THEN 0 ELSE Price_Forosh - Price_KHarid  END)) END ) 
           WHEN Kind_Flag IN ( 2, 3, 4, 8 ) THEN
               Price_KHarid
       END AS Price,
       Takhfif_Flag
FROM TmpHavaleh WITH (NOLOCK)
    INNER JOIN TmpHeadH WITH (NOLOCK)
        ON TmpHeadH.Id_Havaleh = dbo.TmpHavaleh.Id_Havaleh
    INNER JOIN dbo.KalaId WITH (NOLOCK)
        ON KalaId.K_Code = TmpHavaleh.K_Code
    CROSS JOIN dbo.Over_Under WITH (NOLOCK)
WHERE TmpHeadH.Id_Havaleh = @FactorId
      AND Active = 1 
	  AND ShowPrice <> (CASE WHEN @KindFlag = 8 THEN -1 ELSE 0 End)
      AND Action_Code = @KindFlag 
      AND CONVERT(VARCHAR(10), GETDATE(), 111) BETWEEN AzTarikh AND TaTarikh 
ORDER BY SortDisplay;

UPDATE @CalcIncrementalDeductionRow SET Price = 0
WHERE MaxPercent = 0 AND Maxprice = 0

Declare CursorIncrementalDeductionRow Cursor For
SELECT Id, GoodsId, Type, Qty*Price , MaxPercent, MaxPrice, IsCalculateTax, IsDepencencies
FROM @CalcIncrementalDeductionRow

Open CursorIncrementalDeductionRow
Fetch Next From CursorIncrementalDeductionRow Into  @Id, @GoodsId, @Type, @Price, @MaxPercent, @MaxPrice,@IsCalculateTax, @IsDepencencies
While @@FETCH_STATUS = 0 
BEGIN
	IF @MaxPrice > 0
	BEGIN
        IF (ISNULL(@Price,0) > @MaxPrice)
		BEGIN  				
		  SET @TmpPer = @MaxPrice * 100/@Price

		  IF @DeviceShow = 2 
			 UPDATE @CalcIncrementalDeductionRow SET [Percent] = @TmpPer,Price = @MaxPrice WHERE Id = @Id

          ELSE 
			 UPDATE @CalcIncrementalDeductionRow Set [Percent] = @TmpPer, Price = @Price WHERE Id = @Id

        END
		ELSE
		  UPDATE @CalcIncrementalDeductionRow Set [Percent] = @MaxPrice * 100/@Price, Price = @MaxPrice WHERE Id = @Id

	END 
	ELSE IF @MaxPercent > 0 
	  UPDATE @CalcIncrementalDeductionRow Set [Percent] = @MaxPercent, Price = @Price * @MaxPercent/100  WHERE Id = @Id

    Fetch Next From CursorIncrementalDeductionRow Into @Id , @GoodsId, @Type, @Price,@MaxPercent, @MaxPrice,@IsCalculateTax,@IsDepencencies
End
Close CursorIncrementalDeductionRow
Deallocate CursorIncrementalDeductionRow

INSERT INTO dbo.Tmp_Over_Under_Row(AutoId, Name, Type, Code_Over_Under, Max_Percent, Max_Price, ShowPrice, Price_Row,Price,[Percent])
SELECT GoodsId ,Name, Type, IncrementalDeductionCode, MaxPercent, MaxPrice, DeviceShow, IsCalculateTax,Price,[Percent] FROM @CalcIncrementalDeductionRow
WHERE NOT EXISTS(SELECT * FROM dbo.Tmp_Over_Under_Row WHERE dbo.Tmp_Over_Under_Row.Code_Over_Under = IncrementalDeductionCode AND [@CalcIncrementalDeductionRow].GoodsId = dbo.Tmp_Over_Under_Row.AutoId)

------------------------------------------(Calc Tax Goods Row)--------------------------------------------------
DECLARE CursorTax CURSOR FOR
SELECT GoodsId FROM @CalcIncrementalDeductionRow
WHERE Type = 1 AND IsDepencencies = 0 AND IsCalculateTax = 1
GROUP BY GoodsId
OPEN CursorTax
FETCH NEXT FROM CursorTax INTO @GoodsId
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC dbo.Sp_Duty_Row @FactorId, @GoodsId, @KindFlag
  FETCH NEXT FROM CursorTax INTO @GoodsId
END 
UPDATE dbo.Tmp_Over_Under_Row SET Price = ROUND(price,0)
WHERE AutoId IN(SELECT AutoId FROM dbo.TmpHavaleh WHERE Id_Havaleh = @FactorId)
AND Price <> ROUND(price,0)
CLOSE CursorTax
DEALLOCATE CursorTax

